﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Text;

namespace PM.PMBasic
{
    /// <summary>
    /// DictData 的摘要说明
    /// </summary>
    public class DictData
    {
        public DictData()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 更新一个院系的名称（执行事务）
        /// </summary>
        /// <param name="rolename">学院名称</param>
        /// <param name="id">院系编号</param>
        public int Update_DictCollege(string collegename, int id)
        {
            try
            {
                //不用hashtable来执行事务是因为hashtable按他的内部排序将sql语句排乱了
                List<string> sqlstr = new List<string>();
                StringBuilder sqlstr1 = new StringBuilder("update Sys_Account set depart='");
                sqlstr1.Append(collegename).Append("' where depart=(select collegename from Dict_College where collegeid=").Append(id).Append(")");
                StringBuilder sqlstr2 = new StringBuilder("update Bus_Project set pcollege='").Append(collegename).Append("' where pcollege=(select collegename from Dict_College where collegeid=").Append(id).Append(")");
                StringBuilder sqlstr3 = new StringBuilder("update Dict_College set collegename='").Append(collegename).Append("',regisdate=getdate() where collegeid=").Append(id);
                sqlstr.Add(sqlstr1.ToString());
                sqlstr.Add(sqlstr2.ToString());
                sqlstr.Add(sqlstr3.ToString());
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新一个学科一级类的名称（事务处理）
        /// </summary>
        /// <param name="subjectname">学科一级类名称</param>
        /// <param name="id">学科编号</param>
        public int Update_DictSubject(string subjectname, int id)
        {
            try
            {
                //不用hashtable来执行事务是因为hashtable按他的内部排序将sql语句排乱了
                List<string> sqlstr = new List<string>();
                StringBuilder sqlstr1 = new StringBuilder("update Bus_Project set sub2='");
                sqlstr1.Append(subjectname).Append("' where  sub2=(select subject2 from Dict_Subject where id=").Append(id).Append(") and sub1=");
                sqlstr1.Append("(select subject1 from Dict_Subject where id=").Append(id).Append(")");
                StringBuilder sqlstr2 = new StringBuilder("update Dict_Subject set subject2='").Append(subjectname).Append("' where id=").Append(id);
                sqlstr.Add(sqlstr1.ToString());
                sqlstr.Add(sqlstr2.ToString());
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新一个学科门（事务处理）
        /// </summary>
        /// <param name="id">学科门名称</param>
        /// <returns>返回影响行数</returns>
        public int Update_DictSubject1(string oldname, string newname)
        {
            try
            {
                //不用hashtable来执行事务是因为hashtable按他的内部排序将sql语句排乱了
                List<string> sqlstr = new List<string>();
                StringBuilder sqlstr1 = new StringBuilder("update Bus_Project set sub1='");
                sqlstr1.Append(newname).Append("' where  sub1='").Append(oldname).Append("'");
                StringBuilder sqlstr2 = new StringBuilder("update Dict_Subject set subject1='").Append(newname).Append("' where subject1='").Append(oldname).Append("'");
                sqlstr.Add(sqlstr1.ToString());
                sqlstr.Add(sqlstr2.ToString());
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新一个教执委的名称（事务处理）
        /// </summary>
        /// <param name="subjectname">教执委名称</param>
        /// <param name="id">教执委编号</param>
        /// <returns>返回受影响行数</returns>
        public int Update_DictCommit(string commitname, int id)
        {
            try
            {
                //不用hashtable来执行事务是因为hashtable按他的内部排序将sql语句排乱了
                List<string> sqlstr = new List<string>();
                StringBuilder sqlstr1 = new StringBuilder("update Bus_Project set pcommit='");
                sqlstr1.Append(commitname).Append("' where pcommit=(select commitee from Dict_Commit where id=").Append(id).Append(")");
                StringBuilder sqlstr2 = new StringBuilder("update Dict_Commit set commitee='").Append(commitname).Append("' where id=").Append(id);
                sqlstr.Add(sqlstr1.ToString());
                sqlstr.Add(sqlstr2.ToString());
                return PM.Code.DAL.DbHelper.ExecuteSqlTran(sqlstr);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新一个学科一级类的状态
        /// </summary>
        /// <param name="id">学科编号</param>
        /// <param name="value">值</param>
        /// <returns>返回影响行数</returns>
        public int Update_DictSubjectStatus(int id, string value)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@status",SqlDbType.NVarChar)};
                DictSqlParameter[0].Value = id;
                DictSqlParameter[1].Value = value;
                StringBuilder sqlstr = new StringBuilder("update Dict_Subject set isuse=@status where id=@id");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新一个类型的申请模板
        /// </summary>
        /// <param name="templateid">模板编号</param>
        /// <param name="id">类型编号</param>
        public int Update_DictTemplateType(string template, int id)
        {
            try
            {
                int templateid = Int32.Parse(template);
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@templateid",SqlDbType.Int),
                new SqlParameter("@id",SqlDbType.Int)};
                DictSqlParameter[0].Value = templateid;
                DictSqlParameter[1].Value = id;
                StringBuilder sqlstr = new StringBuilder("update Sys_Guide set templateid=@templateid where id=@id");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新一个教执委的状态
        /// </summary>
        /// <param name="id">教执委编号</param>
        /// <param name="value">值</param>
        /// <returns>返回影响行数</returns>
        public int Update_DictCommitStatus(int id, string value)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@status",SqlDbType.NVarChar)};
                DictSqlParameter[0].Value = id;
                DictSqlParameter[1].Value = value;
                StringBuilder sqlstr = new StringBuilder("update Dict_Commit set isuse=@status where id=@id");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除一个院系
        /// </summary>
        /// <param name="id">院系编号</param>
        /// <returns>返回影响行数</returns>
        public int Delete_DictCollege(int id)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@collegeid",SqlDbType.Int)};
                DictSqlParameter[0].Value = id;
                StringBuilder sqlstr = new StringBuilder("delete from Dict_College  where collegeid=@collegeid");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除一个学科一级类
        /// </summary>
        /// <param name="id">学科一级类编号</param>
        /// <returns>返回影响行数</returns>
        public int Delete_DictSubject(int id)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@id",SqlDbType.Int)};
                DictSqlParameter[0].Value = id;
                StringBuilder sqlstr = new StringBuilder("delete from Dict_Subject  where id=@id");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除一个教执委
        /// </summary>
        /// <param name="id">教执委编号</param>
        /// <returns>返回影响行数</returns>
        public int Delete_DictCommit(int id)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder("delete from Dict_Commit  where id=").Append(id);
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除一个学科门
        /// </summary>
        /// <param name="subject1">学科门名称</param>
        /// <returns>返回影响行数</returns>
        public int Delete_DictSubject1(string subject1)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder("delete from Dict_Subject  where subject1='").Append(subject1).Append("' ");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除一个申报类别（暂不关联删除与其相关的数据，如申报计划与审批计划等）
        /// </summary>
        /// <param name="id">学科门名称</param>
        /// <returns>返回影响行数</returns>
        public int Delete_DictType(int id)
        {
            try
            {
                StringBuilder sqlstr = new StringBuilder("delete from Sys_Guide  where id=").Append(id.ToString());
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 检测该院系拥有注册教师的数目
        /// </summary>
        /// <param name="cname">院系名称</param>
        /// <returns>返回查询结果</returns>
        public int Check_DictCollege(string cname)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@collegename",SqlDbType.NVarChar)};
                DictSqlParameter[0].Value = cname;
                StringBuilder sqlstr = new StringBuilder("select count(*) as result from Sys_Account  where depart=@collegename");
                return Int32.Parse(PM.Code.DAL.DbHelper.GetSingle(sqlstr.ToString(), DictSqlParameter).ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入一个新的院系
        /// </summary>
        /// <param name="collegename">学院名称</param>
        /// <returns>返回影响行数</returns>
        public int Insert_DictCollege(string collegename)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@collegename",SqlDbType.NVarChar)};
                DictSqlParameter[0].Value = collegename;
                StringBuilder sqlstr = new StringBuilder("insert into Dict_College(collegename,regis,incharge,inchargeid,inchargename,regisdate) ");
                sqlstr.Append(" values(@collegename,'已注册','无',null,null,getdate())");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入学科一级类
        /// </summary>
        /// <param name="subject1">学科门</param>
        /// <param name="subject2">学科一级类</param>
        /// <returns>返回受影响行数</returns>
        public int Insert_DictSubject(string subject1, string subject2)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@subject1",SqlDbType.NVarChar),
                new SqlParameter("@subject2",SqlDbType.NVarChar),};
                DictSqlParameter[0].Value = subject1;
                DictSqlParameter[1].Value = subject2;
                StringBuilder sqlstr = new StringBuilder("insert into Dict_Subject(subject1,subject2,flag,isuse) ");
                sqlstr.Append(" values(@subject1,@subject2,'0','True')");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 插入教执委名称
        /// </summary>
        /// <param name="commitname">教执委名称</param>
        /// <param name="back">本科（专科）</param>
        /// <returns>返回受影响行数</returns>
        public int Insert_DictCommit(string commitname, int back)
        {
            try
            {
                SqlParameter[] DictSqlParameter ={ 
                new SqlParameter("@commitee",SqlDbType.NVarChar),
                new SqlParameter("@back",SqlDbType.SmallInt),};
                DictSqlParameter[0].Value = commitname;
                DictSqlParameter[1].Value = back;
                StringBuilder sqlstr = new StringBuilder("insert into Dict_Commit(commitee,commitcode,isuse,type) ");
                sqlstr.Append(" values(@commitee,'0','True',@back)");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), DictSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
    }
}
